﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SQLite;

namespace WCFService
{
    public class ItemDA
    {
        SQLiteDA sqliteDA = new SQLiteDA();

        public List<ItemInfo> GetAllItemsInfo()
        {
            var sql = "Select * from items";
            DataTable dt = sqliteDA.GetDataTable(sql);
            var list = new List<ItemInfo>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ItemInfo itemInfo = new ItemInfo();
                    itemInfo.RowID = Int32.Parse(dt.Rows[i]["RowID"].ToString());
                    itemInfo.ItemID = dt.Rows[i]["ItemID"].ToString();
                    itemInfo.ItemName = dt.Rows[i]["ItemName"].ToString();
                    itemInfo.ItemDescr = dt.Rows[i]["ItemDescr"].ToString();
                    itemInfo.Price = decimal.Parse(dt.Rows[i]["Price"].ToString());
                    itemInfo.PhotoUrl = dt.Rows[i]["PhotoUrl"].ToString();
                    list.Add(itemInfo);
                }
            }
            return list;
        }

        public ItemInfo GetItemInfoByID(string itemID)
        {
            ItemInfo itemInfo = null;
            var sql = string.Format("Select * from items where item_id='{0}'", itemID);
            DataTable dt = sqliteDA.GetDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                itemInfo = new ItemInfo();
                itemInfo.RowID = Int32.Parse(dt.Rows[0]["RowID"].ToString());
                itemInfo.ItemDescr = dt.Rows[0]["ItemID"].ToString();
                itemInfo.ItemName = dt.Rows[0]["ItemName"].ToString();
                itemInfo.ItemDescr = dt.Rows[0]["ItemDescr"].ToString();
                itemInfo.Price = decimal.Parse(dt.Rows[0]["Price"].ToString());
                itemInfo.PhotoUrl = dt.Rows[0]["PhotoUrl"].ToString();
            }
            return itemInfo;
        }

        public int InsertItemInfo(ItemInfo itemInfo)
        {
            var getItemID = "select max(ItemID) from items";
            string result = sqliteDA.ExecuteScalar(getItemID);
            if (!string.IsNullOrEmpty(result))
            {
                itemInfo.ItemID = (Int32.Parse(result) + 1).ToString();
            }
            else
            {
                itemInfo.ItemID = "90000000";
            }

            var sql = "insert into items values(@RowID,@ItemID,@ItemName,@ItemDescr,@PhotoUrl,@Price)";
            var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("RowID", null),
                    new SQLiteParameter("ItemID", itemInfo.ItemID),
                    new SQLiteParameter("ItemName", itemInfo.ItemName),
                    new SQLiteParameter("ItemDescr", itemInfo.ItemDescr),
                    new SQLiteParameter("PhotoUrl", itemInfo.PhotoUrl),
                    new SQLiteParameter("Price", itemInfo.Price),
                };
            return sqliteDA.ExecuteNonQuery(sql, cmdparams);
        }

        public int UpdateItemInfo(ItemInfo itemInfo)
        {
            var sql = "Update items Set ItemName=@ItemName, ItemDescr=@ItemDescr, Price=@Price Where ItemID=@ItemID";
            var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("ItemName", itemInfo.ItemName),
                    new SQLiteParameter("ItemDescr", itemInfo.ItemDescr),
                    new SQLiteParameter("Price", itemInfo.Price),
                    new SQLiteParameter("ItemID", itemInfo.ItemID)
                };
            return sqliteDA.ExecuteNonQuery(sql, cmdparams);
        }

        public int DeleteItemInfo(string itemID)
        {
            var sql = "delete items Where ItemID=@ItemID";
            var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("ItemID", itemID)
                };
            return sqliteDA.ExecuteNonQuery(sql, cmdparams);
        }
    }
}
